﻿using Lufei_CaterModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lufei_CaterDAL
{
    public class CategoryDAL
    {

        /// <summary>
        /// 查询所有商品类别
        /// </summary>
        /// <returns></returns>
        public List<CategoryInfo> GetCategories()
        {
            List < CategoryInfo > list= new List<CategoryInfo>();
           string sql = "select * from CategoryInfo where delflag=0";
           DataTable dt= SqlHelper.ExecuteDataTable(sql);

            if (dt.Rows.Count>0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    //关系转对象
                    list.Add(RowsToCategoryInfo(dr));
                }
            }
            return list;

        }

        public CategoryInfo RowsToCategoryInfo(DataRow dr)
        {
            CategoryInfo info = new CategoryInfo();
            info.CatId = Convert.ToInt32(dr["CatId"]);
            info.CatName = dr["CatName"].ToString();
            info.CatNum = dr["CatNum"].ToString();
            info.Remark = dr["Remark"].ToString();
            return info;
        }

        /// <summary>
        /// 根据id查询数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
         public CategoryInfo GetCategoryInfoByID(int id)
        {
            string sql = "select * from CategoryInfo where delflag=0 and catid="+id;
            DataTable dt=SqlHelper.ExecuteDataTable (sql);
            CategoryInfo ci = null;
            if (dt.Rows.Count>0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    ci=RowsToCategoryInfo (dr);
                }
            }
            return ci;
        }


        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="ci"></param>
        /// <returns></returns>
        public int UpdateCategory(CategoryInfo ci)
        {
            string sql = "update CategoryInfo set CatName=@name,CatNum=@num,Remark=@remark where CatId=@id";

            SQLiteParameter[] param = new SQLiteParameter[]
            {
                new SQLiteParameter("@name",ci.CatName),
                new SQLiteParameter("@num",ci.CatNum),
                new SQLiteParameter("@remark",ci.Remark),
                new SQLiteParameter("@id",ci.CatId),
            };

         return   SqlHelper.ExecuteNonQuery(sql, param);
        }



        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="ci"></param>
        /// <returns></returns>
        public int InsertCategory(CategoryInfo ci)
        {
            string sql = "insert into CategoryInfo(catName,catNum,Remark,DelFlag)values(@name,@num,@remark,@DelFlag)";

            SQLiteParameter[] param = new SQLiteParameter[]
            {
                new SQLiteParameter("@name",ci.CatName),
                new SQLiteParameter("@num",ci.CatNum),
                new SQLiteParameter("@remark",ci.Remark),
                new SQLiteParameter("@DelFlag",ci.DelFlag),
            };
             return   SqlHelper.ExecuteNonQuery(sql, param);
        }

        /// <summary>
        /// 判断选择的商品类别下是否有商品
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public object GetProductsByCatoryId(int id)
        {
            string sql = "SELECT COUNT(*) FROM ProductInfo WHERE DelFlag=0 AND CatId="+id;
            return SqlHelper.ExecuteScalar(sql);
        }


        /// <summary>
        /// 软删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int SoftDeleteById(int id)
        {
            string sql = "update CategoryInfo set delflag=1 where catid="+id;
            return SqlHelper.ExecuteNonQuery(sql);
        }

    }
}
